package hu.szyrt.bug.db;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import org.json.JSONException;
import org.json.JSONObject;

import com.google.android.maps.GeoPoint;
import hu.szyrt.bug.models.Category;
import hu.szyrt.bug.models.PlaceItem;
import hu.szyrt.bug.models.PlaceItemBase;
import hu.szyrt.bug.models.SubCategory;
import hu.szyrt.bug.models.Tour;
import hu.szyrt.bug.overlays.PlaceOverlayItem;
import hu.szyrt.bug.utils.Assets;
import hu.szyrt.bug.utils.Config;
import hu.szyrt.bug.utils.Settings;
import hu.szyrt.bug.utils.Utils;
import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.graphics.drawable.BitmapDrawable;
import android.graphics.drawable.Drawable;
import android.util.Log;
import net.sqlcipher.database.SQLiteDatabase;

public class DBHandler {

	private final static int DB_VERSION = Config.DB_VERSION;
	
	private final static String TABLE_CATEGORIES 	 = "categories",
								TABLE_SUB_CATEGORIES = "subcategories",
								TABLE_PLACES		 = "places",
								TABLE_TOUR			 = "tour";
	
	private Context context;
	private Settings settings;
	private String language;
	private String dbName;
	private File dbFile;
	
	private SQLiteDatabase db;
	
	public DBHandler(Context context) {
		this.context = context;
		this.settings = new Settings(context);
		this.language = Utils.supportedLanguage(Locale.getDefault().getLanguage());
		this.dbName = "BUG_2_0_" + language.toUpperCase() + ".db";
		this.dbFile = new File("/data/data/" + context.getPackageName() + "/" + dbName);
		
		SQLiteDatabase.loadLibs(context);
	}
	
	// Handling ------------------------------------------
	public void open(){
		db = SQLiteDatabase.openDatabase(dbFile.getAbsolutePath(), 
											  Config.BUG_DB_PWD, 
											  null,
											  SQLiteDatabase.OPEN_READONLY);
	}
	
	public boolean isOpen(){
		return db != null && db.isOpen();
	}
	
	public void close(){
		if(isOpen()) db.close();
	}
	
	
	// Queries --------------------------------------------
	public List<Category> loadCategories(){
		// Lekérdezés
		String sql = "SELECT * FROM " + getTable(TABLE_CATEGORIES)  + " ORDER BY order_id";
		Cursor c = runQuery(sql);
		
		// Átalakítás
		int indexID = c.getColumnIndex(getField("idcategories")),
			indexName = c.getColumnIndex("category_name"),
			indexDescription = c.getColumnIndex("category_description"),
			indexImageName = c.getColumnIndex("category_img_name"),
			indexSubCategoriesID = c.getColumnIndex("category_subcategories_ids"), 
			indexType = c.getColumnIndex("category_type");
		ArrayList<Category> categories = new ArrayList<Category>();
		while(!c.isAfterLast()){
			categories.add(new Category(
					c.getInt(indexID),					
					c.getString(indexName),
					c.getString(indexDescription),
					getImageName(c.getString(indexImageName)),
					c.getString(indexSubCategoriesID),
					c.getString(indexType)
					));
			c.moveToNext();
		}
		c.close();
		return categories;
	}
	
	public List<SubCategory> loadSubCategories(Category parentCategory){
		// Lekérdezés
		String sql = "SELECT * FROM " + getTable(TABLE_SUB_CATEGORIES) + " WHERE " + getField("idsubcategories") + " IN (" + parentCategory.getSubcategoryIDs() + ")";
		Cursor c = runQuery(sql);
		
		// Átalakítás
		int indexID = c.getColumnIndex(getField("idsubcategories")),
			indexName = c.getColumnIndex("subcategory_name"),
			indexImageName = c.getColumnIndex("subcategory_img_name"),
			indexDescription = c.getColumnIndex("subcategory_description");
			
		if(indexImageName == -1) indexImageName = c.getColumnIndex("subcategory_imgs_name");
		
		ArrayList<SubCategory> categories = new ArrayList<SubCategory>();
		while(!c.isAfterLast()){
			categories.add(new SubCategory(
					c.getInt(indexID),
					c.getString(indexName),
					getImageName(c.getString(indexImageName)),
					c.getString(indexDescription)
					));
			c.moveToNext();
		}
		c.close();
		return categories;
	}
	
	
	public List<PlaceItemBase> loadPlaces(Category parentCategory, boolean bySubCategory, String searchTerms){
		// 0.a Kategória függő feltételek
		ArrayList<String> wheres = new ArrayList<String>();
		if(parentCategory != null){
			//if(parentCategory.getImageName() != null && parentCategory.getImageName().equals("deals"))
			if(parentCategory.getImageName() != null && parentCategory.getImageName().equals("deals.png"))
				wheres.add("place_discount_type <> 10");
			else if(bySubCategory)
				wheres.add("places_subcategories_id=" + parentCategory.getId());
			else
				wheres.add("places_categories_id=" 	+ parentCategory.getId());
		}
		
		// 0.b Szűrési feltételek		
		if(searchTerms != null){
			searchTerms = searchTerms.trim();
			if(searchTerms.length() > 0)		
				wheres.add("(place_name LIKE '%" + searchTerms + "%' OR place_description LIKE '%" + searchTerms + "%')");
		}
		
		// 0.c Állandó feltételek
		wheres.add("deleted <> 1");
		
		// 1. Lekérdezés
		String sql = "SELECT " + getField("idplaces") + ", " +
								"place_name" + ", " +
								"place_description" + ", " +								
								"place_imgs_name" + ", " +
								"place_must_see_number" + ", " +
								"place_discount_type" + " " + 
					 "FROM " + getTable(TABLE_PLACES) + " " +
					 (wheres.size() > 0 ? "WHERE " + Utils.join(wheres, " AND ") : "") + " " +
					 "ORDER BY " +
					 			"place_discount_type ASC, " +
					 			"place_discount_value DESC, " +
					 			"place_name ASC";
		Cursor c = runQuery(sql);
		
		// 2. Átalakítás:  {"img_map":"bambi_map","img_1":"bambi1","img_2":"bambi2","img_polaroid":"bambi1_polaroid"}
		
		int indexID = c.getColumnIndex(getField("idplaces")),
			indexName = c.getColumnIndex("place_name"),
			indexDescription = c.getColumnIndex("place_description"),
			indexImages = c.getColumnIndex("place_imgs_name"),
			indexHighlights = c.getColumnIndex("place_must_see_number"),
			indexDiscountType = c.getColumnIndex("place_discount_type");
		ArrayList<PlaceItemBase> places = new ArrayList<PlaceItemBase>();
		while(!c.isAfterLast()){
			try {
				JSONObject images = new JSONObject(c.getString(indexImages));
				places.add(new PlaceItemBase(
						c.getInt(indexID),
						c.getString(indexName),
						c.getString(indexDescription),						
						getImageName(images == null ? null : images.getString("img_polaroid")),
						c.getInt(indexHighlights),
						c.getInt(indexDiscountType)
						));
				images = null;
			} catch (JSONException e) {
				e.printStackTrace();
			}
			c.moveToNext();
		}
		c.close();
		return places;
	}
	
	// {"img_map":"bambi_map","img_1":"bambi1","img_2":"bambi2","img_polaroid":"bambi1_polaroid"}
	public PlaceItem loadPlace(int id, boolean byHighlightsNumber){
		String where = byHighlightsNumber ? 
							"place_must_see_number=" + id :
							getField("idplaces") + "=" + id;
		String sql = "SELECT * FROM " + getTable(TABLE_PLACES) + " WHERE " + where + " LIMIT 1";
		Cursor c = runQuery(sql);
		if (c != null && c.getCount() > 0) {			
			c.moveToFirst();
			PlaceItem place = null;
			try {
				JSONObject images = new JSONObject(c.getString(c.getColumnIndex("place_imgs_name")));
				place = new PlaceItem(
						c.getInt(c.getColumnIndex(getField("idplaces"))),
						c.getString(c.getColumnIndex("place_name")),
						c.getString(c.getColumnIndex("place_description")),						
						getImageName(images == null ? null : images.getString("img_polaroid")),
						c.getInt(c.getColumnIndex("place_must_see_number")),	
						c.getInt(c.getColumnIndex("place_discount_type")),
						c.getString(c.getColumnIndex("place_opening_hours")),
						c.getString(c.getColumnIndex("place_telephone")),
						c.getString(c.getColumnIndex("place_website")),
						c.getString(c.getColumnIndex("place_address")),
						getImageName(images == null ? null : (!images.isNull("img_map") ? images.getString("img_map") : null)),
						getImageName(images == null ? null : (!images.isNull("img_1") ? images.getString("img_1") : null)),
						getImageName(images == null ? null : (!images.isNull("img_2") ? images.getString("img_2") : null)),
						c.getInt(c.getColumnIndex("place_tour_id")),
						c.getInt(c.getColumnIndex("place_tour_number")),					
						c.getString(c.getColumnIndex("place_modify_date")),						
						c.getInt(c.getColumnIndex("place_discount_value")),
						c.getString(c.getColumnIndex("place_discount_description")),
						c.getInt(c.getColumnIndex("place_number_of_discount")),					
						c.getString(c.getColumnIndex("place_discount_start_date")),
						c.getString(c.getColumnIndex("place_discount_end_date")),
						c.getString(c.getColumnIndex("place_discount_modify_date")),
						c.getInt(c.getColumnIndex("place_number_of_discount_remaining")),
						c.getFloat(c.getColumnIndex("place_gps_latitude")),
						c.getFloat(c.getColumnIndex("place_gps_longitude"))
				);
			} catch (JSONException e) {
				e.printStackTrace();				
			} catch (ParseException e) {
				e.printStackTrace();
			}
			c.close();
			return place;
		} else {			
			return null;
		}
	}
	
	
	public List<Tour> loadTours(){
		// Lekérdezés
		String sql = "SELECT * FROM " + getTable(TABLE_TOUR) + " ORDER BY " + getField("idtour") + " ASC";
		Cursor c = runQuery(sql);
		
		// Átalakítás
		ArrayList<Tour> tours = new ArrayList<Tour>();
		while(!c.isAfterLast()){
			tours.add(new Tour(
					c.getInt(c.getColumnIndex(getField("idtour"))),
					c.getString(c.getColumnIndex("tour_name")),
					c.getString(c.getColumnIndex("tour_description")),
					getImageName(c.getString(c.getColumnIndex("tour_img")))
					));
			c.moveToNext();
		}
		c.close();		
		return tours;
	}
	
	public List<PlaceItemBase> loadTourPlaces(Tour tour){
		
		String orderBy;
		ArrayList<String> wheres = new ArrayList<String>();
		if(tour == null){
			wheres.add("place_tour_id > 4");
			orderBy = "place_tour_id ASC";
		}else{ 
			wheres.add("place_tour_id=" + tour.getId());
			orderBy = "place_tour_number ASC";
		}
		wheres.add("deleted <> 1");
		
		// 1. Lekérdezés
		String sql = "SELECT " + getField("idplaces") + ", " +
								"place_name" + ", " +
								"place_description" + ", " +								
								"place_imgs_name" + ", " +
								"place_must_see_number" + ", " +
								"place_discount_type" + " " + 
					 "FROM " + getTable(TABLE_PLACES) + " " +
					 (wheres.size() > 0 ? "WHERE " + Utils.join(wheres, " AND ") : "") + " " +
					 (orderBy != null ? "ORDER BY " + orderBy : "");
		Cursor c = runQuery(sql);
		
		// 2. Átalakítás
		// {"img_map":"bambi_map","img_1":"bambi1","img_2":"bambi2","img_polaroid":"bambi1_polaroid"}
		ArrayList<PlaceItemBase> places = new ArrayList<PlaceItemBase>();
		while(!c.isAfterLast()){
			try {
				JSONObject images = new JSONObject(c.getString(c.getColumnIndex("place_imgs_name")));
				places.add(new PlaceItemBase(
						c.getInt(c.getColumnIndex(getField("idplaces"))),
						c.getString(c.getColumnIndex("place_name")),
						c.getString(c.getColumnIndex("place_description")),						
						getImageName(images == null ? null : images.getString("img_polaroid")),
						c.getInt(c.getColumnIndex("place_must_see_number")),
						c.getInt(c.getColumnIndex("place_discount_type"))
						));
				images = null;
			} catch (JSONException e) {
				e.printStackTrace();
			}
			c.moveToNext();
		}
		c.close();
		return places;
	}
	
	
	
	
	public List<PlaceOverlayItem> loadOverlayPlaces(Category category, int placeID, Assets assets){		
		ArrayList<String> wheres = new ArrayList<String>();		
		if(category != null)
			wheres.add("places_categories_id=" + category.getId());
		if(placeID > 0)
			wheres.add(getField("idplaces")+ "=" + placeID);
		wheres.add("deleted <> 1");
		
		// 1. Lekérdezés
		String sql = "SELECT " + getField("idplaces") + ", " +
								"place_name" + ", " +
								"place_address" + ", " +
								"place_gps_latitude" + ", " +
								"place_gps_longitude" + " " +																 
					 "FROM " + getTable(TABLE_PLACES) + " " +
					 (wheres.size() > 0 ? "WHERE " + Utils.join(wheres, " AND ") : "") + " " +
					 "ORDER BY " +
					 			"place_tour_id ASC";
		Cursor c = runQuery(sql);
		
		// 2. Átalakítás
		// {"img_map":"bambi_map","img_1":"bambi1","img_2":"bambi2","img_polaroid":"bambi1_polaroid"}
		Drawable markerDrawable = new BitmapDrawable(assets.getContext().getResources(), assets.loadCategoryIcon(category.getImageName(), 2));
		ArrayList<PlaceOverlayItem> places = new ArrayList<PlaceOverlayItem>();
		while(!c.isAfterLast()){			
			PlaceOverlayItem poi = new PlaceOverlayItem(
						c.getInt(c.getColumnIndex(getField("idplaces"))),
						new GeoPoint(
								(int)(c.getFloat(c.getColumnIndex("place_gps_latitude")) * 1E6),
								(int)(c.getFloat(c.getColumnIndex("place_gps_longitude")) * 1E6)
								),
						c.getString(c.getColumnIndex("place_name")),
						c.getString(c.getColumnIndex("place_address"))
					);			
			poi.setMarkerBounded(markerDrawable);
			poi.setIcon(markerDrawable);
			places.add(poi);
			c.moveToNext();
		}
		c.close();
		return places;
	}
	
	
	public void dealReduceRemaining(int placeID){
		String sql = "UPDATE " + getTable(TABLE_PLACES) + " " +
					 "SET place_number_of_discount_remaining = place_number_of_discount_remaining - 1" + " " +
					 "WHERE " + getField("idplaces") + "=" + placeID;
		open();
		db.rawExecSQL(sql);
		close();
	}
	
	
	// Other ----------------------------------------------
	private Cursor runQuery(String sql){
		Log.d("bug", sql);
		open();
		Cursor c = db.rawQuery(sql, null);
		c.moveToFirst();
		close();
		return c;
	}
	
	private String getTable(String tableName){
		return tableName + "_" + language.toLowerCase();
	}
	
	private String getField(String field){
		return field + "_" + language.toLowerCase();
	}
	
	private String getImageName(String imageName){
		return imageName;
		/*
		if(imageName == null) return null;
		else return imageName.replace(".png", "").replace(".jpg", "");
		*/
	}
	
	
	// INSTALLER ------------------------------------------
	public boolean isInstalled(){		
		return this.dbFile.exists() && DB_VERSION == getDBVersion();
	}
	
	public void install() throws IOException{
		AssetManager assetManager = context.getAssets();
		InputStream in = assetManager.open(dbName);
		OutputStream out = new FileOutputStream(dbFile);
		Utils.copyFile(in, out);
		out.flush();
		out.close();
		in.close();
		settings.save(getField(Settings.KEY_DB_VERSION), DB_VERSION);
	}
	
	public int getDBVersion(){
		try{
			return Integer.parseInt((String)settings.load(getField(Settings.KEY_DB_VERSION)));
		}catch (NumberFormatException e) {
			return -1;
		}
	}
	
	
	// Getters ----------------------------------------------
	public String getLanguage() {
		return language;
	}
	
}
